select    a11.UnitOfMeasurePrimary UnitOfMeasurePrimary,
    a11.UnitOfMeasureAsInput UnitOfMeasureAsInput,
    a12.PriceOverrideCode PriceOverrideCode,
    a11.ParentNumber ParentNumber,
    a11.OrderTypeCode OrderTypeCode,
    a11.OrderNumber OrderNumber,
    a11.LineNumber LineNumber,
    a11.ItemNumberID ItemNumberID,
    a11.InvoiceDateID InvoiceDateID,
    a12.DocumentType DocumentType,
    a12.DocumentNumber DocumentNumber,
    a11.CustomerNumberID CustomerNumberID,
    sum(a11.QuantityShipped) WJXBFS1,
    sum(a11.ExtendedPrice) WJXBFS2,
    sum((a11.LB * a11.QuantityShipped)) WJXBFS3,
    sum((a11.LT * a11.QuantityShipped)) WJXBFS4,
    sum(a11.ExtendedCost) WJXBFS5,
    sum((a11.CS * a11.QuantityShipped)) WJXBFS6,
    sum((a11.EA * a11.QuantityShipped)) WJXBFS7
into #ZZTI8AIUCE7MD000
from    Sales_Star_Facts    a11
    join    Sales_Star_FactsExt    a12
     on     (a11.FactsExtID = a12.FactsExtID)
    join    Sales_Star_Customer    a13
     on     (a11.CustomerNumberID = a13.CustomerNumberID)
    join    Common_Star_Dates    a14
     on     (a11.InvoiceDateID = a14.CompanyDateID)
where    (a11.TransactionType = '2'
and a11.OrderTypeCode in ('CO', 'SF', 'SD', 'SO', 'SU')
and a14.Dates between '2011-01-01' and '2012-02-29'
and a13.ParentNumber in (5071296)
and a11.ItemNumber02 not in ('Advanced Pricing Adj Line', 'SURCHARGE'))
group by    a11.UnitOfMeasurePrimary,
    a11.UnitOfMeasureAsInput,
    a12.PriceOverrideCode,
    a11.ParentNumber,
    a11.OrderTypeCode,
    a11.OrderNumber,
    a11.LineNumber,
    a11.ItemNumberID,
    a11.InvoiceDateID,
    a12.DocumentType,
    a12.DocumentNumber,
    a11.CustomerNumberID OPTION (MERGE JOIN, LOOP JOIN)

select    a12.ShiptoNumber ShiptoNumber,
    a12.ShiptoNumber ShiptoNumber0,
    a11.OrderTypeCode OrderTypeCode,
    a11.OrderNumber OrderNumber,
    a11.LineNumber LineNumber,
    a11.ItemNumberID ItemNumberID,
    sum(a11.PL_UnitPrice) WJXBFS1
into #ZZTB5NTV8KFMD001
from    Sales_Star_Price_Facts    a11
    join    Sales_Star_Customer    a12
     on     (a11.CustomerNumberID = a12.CustomerNumberID)
    join    Sales_Star_PriceLedger    a13
     on     (a11.LineNumber = a13.LineNumber and
    a11.OrderNumber = a13.OrderNumber and
    a11.OrderNumberDocumentCompany = a13.OrderNumberDocumentCompany and
    a11.OrderTypeCode = a13.OrderTypeCode and
    a11.PriceAdjustmentKeyID = a13.PriceAdjustmentKeyID and
    a11.SourceID = a13.SourceID)
    join    Sales_Star_Facts    a14
     on     (a11.InvoiceDateID = a14.InvoiceDateID and
    a11.ItemNumberID = a14.ItemNumberID and
    a11.LineNumber = a14.LineNumber and
    a11.OrderNumber = a14.OrderNumber and
    a11.OrderNumberDocumentCompany = a14.OrderNumberDocumentCompany and
    a11.OrderTypeCode = a14.OrderTypeCode and
    a11.ShiptoNumber = a14.ShiptoNumber and
    a11.SourceID = a14.SourceID)
where    (a11.OrderTypeCode in ('CO', 'SF', 'SD', 'SO', 'SU')
and a12.ParentNumber in (5071296)
and a14.ItemNumber02 not in ('Advanced Pricing Adj Line', 'SURCHARGE')
and a13.SequenceNumber between 902 and 998)
group by    a12.ShiptoNumber,
    a12.ShiptoNumber,
    a11.OrderTypeCode,
    a11.OrderNumber,
    a11.LineNumber,
    a11.ItemNumberID OPTION (MERGE JOIN, LOOP JOIN)

select    distinct a12.ShiptoNumber ShiptoNumber,
    a12.ShiptoName ShiptoName,
    a12.ShiptoNumber ShiptoNumber0,
    a17.AlphaName AlphaName,
    pa11.InvoiceDateID InvoiceDateID,
    a15.Dates Dates,
    pa11.OrderNumber OrderNumber,
    pa11.LineNumber LineNumber,
    pa11.OrderTypeCode OrderTypeCode,
    a14.L1_Code L1_Code,
    a14.L1 UserDefinedCodesValues,
    a14.ItemNumber02 ItemNumber02,
    a14.Description01 Description010,
    pa11.UnitOfMeasurePrimary UnitOfMeasurePrimary,
    pa11.UnitOfMeasureAsInput UnitOfMeasureAsInput,
    pa11.DocumentNumber DocumentNumber,
    pa11.DocumentType DocumentType,
    pa11.PriceOverrideCode PriceOverrideCode,
    a14.L2_surrogatekey L2_Code,
    a14.L2 UserDefinedCodesValues0,
    a15.FiscalMonth FiscalMonth,
    a15.FiscalMonthName FiscalMonthName,
    a12.ParentNumber ParentNumber,
    a12.ParentName ParentName,
    pa11.ParentNumber ParentNumber0,
    a12.ShiptoSearchTypeCode ShiptoSearchTypeCode,
    a12.ParentStateCode ParentStateCode,
    a12.ParentState ParentState,
    a12.ParentCity ParentCity,
    a12.ParentAddressLine01 ParentAddressLine01,
    a12.ParentNumber ParentNumber1,
    a16.AlphaName AlphaName0,
    pa11.CustomerNumberID CustomerNumberID,
    LEFT(a12.CustomerNumberID, (LEN(a12.CustomerNumberID) - 10)) CustCol_4,
    pa11.ItemNumberID ItemNumberID,
    pa11.WJXBFS1 WJXBFS1,
    pa11.WJXBFS2 WJXBFS2,
    pa11.WJXBFS3 WJXBFS3,
    pa11.WJXBFS4 WJXBFS4,
    (ISNULL(pa11.WJXBFS2, 0) + ISNULL((pa13.WJXBFS1 * pa11.WJXBFS1), 0)) NetSales,
    ISNULL((pa13.WJXBFS1 * pa11.WJXBFS1), 0) WJXBFS5,
    pa11.WJXBFS5 WJXBFS6,
    pa11.WJXBFS6 WJXBFS7,
    pa11.WJXBFS7 WJXBFS8
from    #ZZMD00    pa11
    join    Sales_Star_Customer    a12
     on     (pa11.CustomerNumberID = a12.CustomerNumberID)
    left outer join    #ZZMD01    pa13
     on     (a12.ShiptoNumber = pa13.ShiptoNumber and
    a12.ShiptoNumber = pa13.ShiptoNumber0 and
    pa11.ItemNumberID = pa13.ItemNumberID and
    pa11.LineNumber = pa13.LineNumber and
    pa11.OrderNumber = pa13.OrderNumber and
    pa11.OrderTypeCode = pa13.OrderTypeCode)
    join    Common_Star_Item    a14
     on     (pa11.ItemNumberID = a14.ItemNumberID)
    join    Common_Star_Dates    a15
     on     (pa11.InvoiceDateID = a15.CompanyDateID)
    join    Common_Star_AddressBook    a16
     on     (a12.ParentNumber = a16.AddressNumber)
    join    Common_Star_AddressBook    a17
     on     (a12.ShiptoNumber = a17.AddressNumber)
